package com.banksoft.util;

import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2016/2/20.
 */
public class ExcelUtil {

    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel

    public  static Workbook createWorkBook(List<JSONObject> data,String[] columns,String[] keys){
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
       for(int i=0;i<columns.length;i++){
            sheet.setColumnWidth(i,5000);
        }

        CellStyle css1 = workbook.createCellStyle();
        CellStyle css2 = workbook.createCellStyle();

        Font f1 = workbook.createFont();
        f1.setFontHeightInPoints((short) 10);
        f1.setColor(IndexedColors.BLACK.getIndex());
        f1.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font f2 = workbook.createFont();
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

        css1.setFont(f1);
        css1.setAlignment(CellStyle.ALIGN_CENTER);
        css2.setFont(f2);
        css2.setAlignment(CellStyle.ALIGN_CENTER);

        JSONObject total = data.get(data.size() - 1);
        data.remove(data.size() - 1);

        Row totalRow = sheet.createRow(0);
        Cell sucCell = totalRow.createCell(0);
        sucCell.setCellValue("成功订单:"+total.get("sucOrder")+"  金额："+total.get("suc").toString());
        Cell failCell = totalRow.createCell(1);
        failCell.setCellValue("失败订单:"+total.get("failOrder")+"  金额："+total.get("fail").toString());



        Row row = sheet.createRow(1);
        for(int i=0;i<columns.length;i++){
          //  sheet.setColumnWidth(i,120);
            Cell cell = row.createCell(i);
            cell.setCellValue(columns[i]);
            cell.setCellStyle(css1);
        }



        for(int i=0;i<data.size();i++){
            Row row1 = sheet.createRow((short) i+2);
            JSONObject json = data.get(i);
            // 在row行上创建一个方格
             for(short j=0;j<keys.length;j++){
                Cell cell = row1.createCell(j);
                cell.setCellValue(json.get(keys[j]) == null?" ": json.get(keys[j]).toString());
                cell.setCellStyle(css2);
            }
        }

        return workbook;
    }

    /**
     * 读取excel
     * @param filePath
     * @return
     * @throws Exception
     */
    public static List<Map> readExcel(String filePath) throws Exception{
        String fileType = filePath.substring(filePath.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
             return readXls(filePath);
         } else if (excel2007U.equals(fileType)) {
            return readXlsx(filePath);
        }
        return null;
    }

    public static List<Map> readXlsx(String path) throws IOException {
              InputStream is = new FileInputStream(path);
              XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
              List<Map> list = new ArrayList<Map>();
              for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
                  XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
                  if (xssfSheet == null) {
                      continue;
                  }
                  // Read the Row
                  for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                      XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                      Map map = new HashMap();
                      if (xssfRow != null) {
                          XSSFCell orderNo = xssfRow.getCell(0);
                          XSSFCell sendNo = xssfRow.getCell(1);
                          map.put("orderNo",getValue(orderNo));
                          map.put("sendNo",getValue(sendNo));
                          list.add(map);
                      }
                  }
               }
             return list;
        }

    public static List<Map> readXls(String path) throws IOException {
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        List<Map> list = new ArrayList<Map>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    HSSFCell orderNo = hssfRow.getCell(0);
                    HSSFCell sendNo = hssfRow.getCell(1);
                    Map map = new HashMap();
                    map.put("orderNo",getValue(orderNo));
                    map.put("sendNo",getValue(sendNo));
                    list.add(map);
                  }
               }
          }
        return list;
       }


    /**
     * 得到Excel表中的值
     *
     * @param hssfCell
     *            Excel中的每一个格子
     * @return Excel中每一个格子中的值
     */
    @SuppressWarnings("static-access")
    private static String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            // 返回字符串类型的值
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    private static String getValue(XSSFCell xssfRow) {
        if (xssfRow.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
     }

}
